package com.cms.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBHelper {

	public static Connection getConn() {
		
//		String url = "jdbc:mysql://120.203.143.14:3306/wqy_blue3316";
//		String user = "wqy_blue3316";
//		String password = "blue19860914";
//		
//		Connection conn = null;
//		try {
//			Class.forName("com.mysql.jdbc.Driver");
//			conn = DriverManager.getConnection(url, user, password);
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (ClassNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		
		DataSource datasource;
		Connection conn = null;
		try {
			Context initcontext = new InitialContext();
			Context context = (Context) initcontext.lookup("java:comp/env");
			datasource = (DataSource) context.lookup("jdbc/cms");
			conn = datasource.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConn(Connection conn) {
			if(conn!=null){
				try{conn.close();}catch(SQLException e){e.printStackTrace();}
			}
	}
	
	public static String getPagingSQL(String sql,String order){
		sql = sql + " order by " + order;
		sql = sql + " limit ?,? ";
		return sql;
	}
	
	@SuppressWarnings("finally")
	public static int getResultCount(Connection conn, String sql_count) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		int count = 0;
		try {
			pst = conn.prepareStatement(sql_count);
			rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
					rs = null;
				} catch (final SQLException e) {
				}
			}
			if (pst != null) {
				try {
					pst.close();
					pst = null;
				} catch (final SQLException e) {
				}
			}
			return count;
		}
	}
}
